Numbers: Automatically extend ranges to new rows?

Question

Level 1

8 points

Numbers: Automatically extend ranges to new rows?

Hi,

I have a spreadsheet that I update daily with a new row. I have a bunch of rows at the bottom of the sheet that do various things (e.g. Sum, Countif etc.) on the columns above. Is there a way of getting the range being used to automatically update to include the new row each day?

For example, if I have =SUM($A$1:A2) in cell A4 and I insert a new row in A3 (copying row A2 and pasting formulas), I'd like the sum to update to =SUM($A$1:A3)

Any ideas thankfully recieved!

Posted on Sep 8, 2015 1:42 AM

Reply

Answer 1

Sep 16, 2015 4:03 AM in response to NotoriousREV

The formula will only pick up additional rows (or columns) if the new row is inserted within the existing range. The formula has no way of determining the addition of data outside of the defined range unless you modify it accordingly. Try including a row where the cell in question is left blank and add the new row in above it.

Reply

Answer 2

t quinn

Level 5

7,063 points

Sep 16, 2015 4:03 AM in response to NotoriousREV

Hi NotoriousREV,

Have you tried converting your summary rows to footers? I think this will get you what you want as long as your formulas include the last (non-footer) row.

Before I simplified a sheet I had multiple ranges within a single column reporting to different places. I needed to add rows as Kilgore-Trout suggests. I kept an unused row below each range and added rows above them.

quinn

Reply

Answer 3

NotoriousREV Author

Level 1

8 points

Sep 16, 2015 4:03 AM in response to NotoriousREV

Thanks both, the blank row method worked fine and has saved me a lot of messing about daily!

Reply

Answer 4

Sep 16, 2015 4:57 AM in response to NotoriousREV

You're welcome. Glad you got it sorted out.

Reply